from datetime import datetime

from extensions import db

class User(db.Model):
    """用户表"""
    __tablename__ = 'user'
    
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    password = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    # 移除last_login字段以匹配现有数据库结构

    def __repr__(self):
        return f'<User {self.username}>'

class UserActual(db.Model):
    """用户表 - 与实际数据库结构匹配"""
    __tablename__ = 'user_info'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))  # 实际数据库字段名为name
    password = db.Column(db.String(100))
    email = db.Column(db.String(100))
    addr = db.Column(db.String(100))
    collect_id = db.Column(db.String(250))
    seen_id = db.Column(db.String(250))

    def __repr__(self):
        return f'<UserActual {self.name}>'
    
    def get_collect_ids(self):
        """获取收藏房源ID列表"""
        if self.collect_id:
            return self.collect_id.split(',')
        return []
    
    def get_seen_ids(self):
        """获取浏览记录房源ID列表"""
        if self.seen_id:
            return self.seen_id.split(',')
        return []
    
    def is_collected(self, house_id):
        """检查房源是否已收藏"""
        return str(house_id) in self.get_collect_ids()
    
    def add_to_collection(self, house_id):
        """添加房源到收藏"""
        collect_ids = self.get_collect_ids()
        if str(house_id) not in collect_ids:
            collect_ids.insert(0, str(house_id))
            # 限制收藏数量为50条
            if len(collect_ids) > 50:
                collect_ids = collect_ids[:50]
            self.collect_id = ','.join(collect_ids)
    
    def remove_from_collection(self, house_id):
        """从收藏中移除房源"""
        collect_ids = self.get_collect_ids()
        if str(house_id) in collect_ids:
            collect_ids.remove(str(house_id))
            self.collect_id = ','.join(collect_ids) if collect_ids else ''
    
    def add_to_seen(self, house_id):
        """添加房源到浏览记录"""
        seen_ids = self.get_seen_ids()
        if str(house_id) not in seen_ids:
            seen_ids.insert(0, str(house_id))
            # 限制浏览记录数量为20条
            if len(seen_ids) > 20:
                seen_ids = seen_ids[:20]
            self.seen_id = ','.join(seen_ids)
    
    def clear_seen(self):
        """清空浏览记录"""
        self.seen_id = ''

class HouseInfoFinal(db.Model):
    """最终修复的MySQL house_info表结构"""
    __tablename__ = 'house_info'
    
    # 根据实际数据库表结构定义字段
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    rooms = db.Column(db.String(100))
    area = db.Column(db.String(100))
    price = db.Column(db.String(100))
    direction = db.Column(db.String(100))  # 朝向
    rent_type = db.Column(db.String(100))  # 租住类型
    region = db.Column(db.String(100))     # 区域
    block = db.Column(db.String(100))     # 板块
    address = db.Column(db.String(200))
    traffic = db.Column(db.String(100))    # 交通信息
    publish_time = db.Column(db.Integer)   # 发布时间戳
    facilities = db.Column(db.Text)        # 配套设施
    highlights = db.Column(db.Text)        # 房源亮点
    matching = db.Column(db.Text)          # 配套信息
    travel = db.Column(db.Text)            # 出行信息
    page_views = db.Column(db.Integer)     # 浏览量
    landlord = db.Column(db.String(30))    # 房东
    phone_num = db.Column(db.String(100))  # 电话号码
    house_num = db.Column(db.String(100))  # 房源编号

    def __repr__(self):
        return f'<HouseInfoFinal {self.title}>'

    @property
    def view_count(self):
        """兼容模板中使用的view_count字段"""
        return self.page_views or 0

    @property
    def area_value(self):
        """提取面积的数值部分"""
        if not self.area:
            return 0.0
        cleaned = ''.join(ch for ch in self.area if ch.isdigit() or ch == '.' or ch == ',')
        if not cleaned:
            return 0.0
        try:
            return float(cleaned.replace(',', ''))
        except ValueError:
            return 0.0

    @property
    def price_value(self):
        """将“5000元/月”或“3.2万”一类的文本价格转换为数字"""
        if not self.price:
            return 0.0
        cleaned = ''.join(ch for ch in self.price if ch.isdigit() or ch == '.' or ch == ',')
        if not cleaned:
            return 0.0
        try:
            value = float(cleaned.replace(',', ''))
        except ValueError:
            return 0.0
        if '万' in self.price and '元' not in self.price:
            value *= 10000
        return value
    
    @property
    def publish_date(self):
        """将时间戳格式化为日期"""
        if not self.publish_time:
            return '未知'
        try:
            return datetime.fromtimestamp(int(self.publish_time)).strftime('%Y-%m-%d')
        except (ValueError, TypeError, OSError):
            return '未知'

    @classmethod
    def get_total_count(cls):
        """获取房源总数"""
        return cls.query.count()
    
    @classmethod
    def get_recommended_houses(cls, limit=6):
        """获取推荐房源（按发布时间倒序）"""
        return cls.query.order_by(cls.publish_time.desc()).limit(limit).all()
    
    @classmethod
    def get_hot_houses(cls, limit=8):
        """获取热点房源（按浏览量倒序）"""
        return cls.query.order_by(cls.page_views.desc()).limit(limit).all()
    
    @classmethod
    def get_beijing_houses(cls, limit=6):
        """获取北京房源"""
        return cls.query.filter(cls.region.like('%北京%')).limit(limit).all()
